﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using EBSystem.Data;

namespace EBSystem.SQL
{
    static public class SQLConnection
    {
        static public SqlConnection sqlConnection { get; set; }
        static public SqlCommand sqlCommand { get; set; }
        static public SqlDataReader sqlDataReader { get; set; }

        static SQLConnection()
        {
            sqlConnection = new()
            {
                ConnectionString =
                   "Data Source=localhost;" +
                   "Initial Catalog=EBSystem;" +
                   "User ID=sa;" +
                   "Password="
            };

            sqlCommand = new()
            {
                CommandType = CommandType.StoredProcedure,
                Connection = sqlConnection
            };
        }

        static public void Connect()
        {
            try
            {
                sqlConnection.Open();
            }
            catch (Exception ex)
            {
                MessageBox.Show("数据库连接失败\r\n错误:" + ex.Message + "\r\n跟踪:" + ex.StackTrace);
            }
        }

        static public bool CreateCustomerAccount(string userName, string userPassword, string nickName, string email, string phoneNumber)
        {
            string strProcName = "EBSystem.SP_CreateCustomerAccount";

            sqlCommand.CommandText = strProcName;

            sqlCommand.Parameters.Add("@userName", SqlDbType.NVarChar, 64);
            sqlCommand.Parameters.Add("@userPassword", SqlDbType.NVarChar, 64);
            sqlCommand.Parameters.Add("@nickName", SqlDbType.NVarChar, 64);
            sqlCommand.Parameters.Add("@email", SqlDbType.NVarChar, 64);
            sqlCommand.Parameters.Add("@phoneNumber", SqlDbType.Char, 11);

            sqlCommand.Parameters["@userName"].Value = userName;
            sqlCommand.Parameters["@userPassword"].Value = userPassword;
            sqlCommand.Parameters["@nickName"].Value = nickName;
            sqlCommand.Parameters["@email"].Value = email;
            sqlCommand.Parameters["@phoneNumber"].Value = phoneNumber;

            sqlCommand.Parameters.Add("@return", SqlDbType.Int);
            sqlCommand.Parameters["@return"].Direction = ParameterDirection.ReturnValue;

            string procReturn;

            try
            {
                sqlCommand.ExecuteNonQuery();
                procReturn = sqlCommand.Parameters["@return"].Value.ToString();

                sqlCommand.Parameters.Clear();
            }
            catch (Exception ex)
            {
                MessageBox.Show("数据库查询失败\r\n错误:" + ex.Message + "\r\n跟踪:" + ex.StackTrace);
                sqlCommand.Parameters.Clear();
                return false;
            }

            if (procReturn == "-1")
                return false;
            return true;
        }

        static public bool CreateBusinessAccount(string userName, string userPassword, string nickName, string email, string phoneNumber)
        {
            string strProcName = "EBSystem.SP_CreateBusinessAccount";

            sqlCommand.CommandText = strProcName;

            sqlCommand.Parameters.Add("@userName", SqlDbType.NVarChar, 64);
            sqlCommand.Parameters.Add("@userPassword", SqlDbType.NVarChar, 64);
            sqlCommand.Parameters.Add("@storeName", SqlDbType.NVarChar, 64);
            sqlCommand.Parameters.Add("@email", SqlDbType.NVarChar, 64);
            sqlCommand.Parameters.Add("@phoneNumber", SqlDbType.Char, 11);

            sqlCommand.Parameters["@userName"].Value = userName;
            sqlCommand.Parameters["@userPassword"].Value = userPassword;
            sqlCommand.Parameters["@storeName"].Value = nickName;
            sqlCommand.Parameters["@email"].Value = email;
            sqlCommand.Parameters["@phoneNumber"].Value = phoneNumber;

            sqlCommand.Parameters.Add("@return", SqlDbType.Int);
            sqlCommand.Parameters["@return"].Direction = ParameterDirection.ReturnValue;
            //sqlCommand.Parameters["@userName"].Direction = ParameterDirection.Output;

            //DataTable dataTable = new();
            //using (SqlDataAdapter adapter = new SqlDataAdapter(sqlCommand))
            //{
            //    adapter.Fill(dataTable);
            //}
            string procReturn;

            try
            {
                //得到输出参数的值 赋值给name 得到的是object类型的
                //var name = sqlCommand.Parameters["@userName"].Value.ToString();
                sqlCommand.ExecuteNonQuery();
                procReturn = sqlCommand.Parameters["@return"].Value.ToString();

                sqlCommand.Parameters.Clear();
            }
            catch (Exception ex)
            {
                MessageBox.Show("数据库查询失败\r\n错误:" + ex.Message + "\r\n跟踪:" + ex.StackTrace);
                sqlCommand.Parameters.Clear();
                return false;
            }

            if (procReturn == "-1")
                return false;
            return true;
        }

        static public long LoginAuthentication(string userName, string userPassword)
        {
            string strProcName = "EBSystem.SP_LoginAuthentication";

            sqlCommand.CommandText = strProcName;

            sqlCommand.Parameters.Add("@userName", SqlDbType.NVarChar, 64);
            sqlCommand.Parameters.Add("@userPassword", SqlDbType.NVarChar, 64);

            sqlCommand.Parameters["@userName"].Value = userName;
            sqlCommand.Parameters["@userPassword"].Value = userPassword;

            sqlCommand.Parameters.Add("@return", SqlDbType.BigInt);
            sqlCommand.Parameters["@return"].Direction = ParameterDirection.ReturnValue;

            string procReturn;

            try
            {
                sqlCommand.ExecuteNonQuery();
                procReturn = sqlCommand.Parameters["@return"].Value.ToString();

                sqlCommand.Parameters.Clear();
            }
            catch (Exception ex)
            {
                MessageBox.Show("数据库查询失败\r\n错误:" + ex.Message + "\r\n跟踪:" + ex.StackTrace);
                sqlCommand.Parameters.Clear();
                return -3;
            }

            long accountID = long.Parse(procReturn);
            if (accountID < 0)
                return accountID;

            strProcName = "EBSystem.SP_Login";

            sqlCommand.CommandText = strProcName;

            sqlCommand.Parameters.Add("@userName", SqlDbType.NVarChar, 64);
            sqlCommand.Parameters["@userName"].Value = userName;

            DataTable dataTable = new();

            try
            {
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader.Read())
                {
                    UserData.accountID = procReturn;
                    UserData.userName = userName;
                    UserData.userPassword = userPassword;
                    UserData.email = sqlDataReader["email"].ToString();
                    UserData.phoneNumber = sqlDataReader["phoneNumber"].ToString();
                    if ("customerAccountID" == sqlDataReader.GetName(0))
                    {
                        UserData.accountType = "用户";
                        UserData.nickName = sqlDataReader["nickName"].ToString();
                    }
                    else if ("businessAccountID" == sqlDataReader.GetName(0))
                    {
                        UserData.accountType = "商家";
                        UserData.storeName = sqlDataReader["storeName"].ToString();
                    }
                    else
                        UserData.accountType = "管理员";
                }
                sqlCommand.Parameters.Clear();
                sqlDataReader.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("数据库查询失败\r\n错误:" + ex.Message + "\r\n跟踪:" + ex.StackTrace);
                sqlCommand.Parameters.Clear();
                sqlDataReader.Close();
                return -3;
            }

            if (UserData.accountType == "用户")
            {
                LoadDeliveryAddress();
            }

            return accountID;
        }

        static public void LoadDeliveryAddress()
        {
            string strProcName = "EBSystem.SP_LoadDeliveryAddress";

            sqlCommand.CommandText = strProcName;

            sqlCommand.Parameters.Add("@customerAccountID", SqlDbType.BigInt);
            sqlCommand.Parameters["@customerAccountID"].Value = UserData.accountID;

            try
            {
                sqlDataReader = sqlCommand.ExecuteReader();
                UserData.deliveryAddress = new();
                UserData.deliveryAddressID = new();
                while (sqlDataReader.Read())
                {
                    List<string> temp = new();
                    temp.Add(sqlDataReader["province"].ToString());
                    temp.Add(sqlDataReader["city"].ToString());
                    temp.Add(sqlDataReader["county"].ToString());
                    temp.Add(sqlDataReader["detailedAddress"].ToString());
                    UserData.deliveryAddress.Add(temp);
                    UserData.deliveryAddressID.Add(sqlDataReader["deliveryAddressID"].ToString());
                }
                sqlCommand.Parameters.Clear();
                sqlDataReader.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("数据库查询失败\r\n错误:" + ex.Message + "\r\n跟踪:" + ex.StackTrace);
                sqlCommand.Parameters.Clear();
                sqlDataReader.Close();
            }
        }

        static public void UpdateCustomerPassword(string userPassword)
        {
            string strProcName = "EBSystem.SP_UpdateCustomerPassword";

            sqlCommand.CommandText = strProcName;

            sqlCommand.Parameters.Add("@customerAccountID", SqlDbType.BigInt);
            sqlCommand.Parameters["@customerAccountID"].Value = UserData.accountID;
            sqlCommand.Parameters.Add("@userPassword", SqlDbType.NVarChar, 64);
            sqlCommand.Parameters["@userPassword"].Value = userPassword;

            sqlCommand.Parameters.Add("@return", SqlDbType.BigInt);
            sqlCommand.Parameters["@return"].Direction = ParameterDirection.ReturnValue;
            //sqlCommand.Parameters["@userName"].Direction = ParameterDirection.Output;

            //DataTable dataTable = new();
            //using (SqlDataAdapter adapter = new SqlDataAdapter(sqlCommand))
            //{
            //    adapter.Fill(dataTable);
            //}

            try
            {
                sqlCommand.ExecuteNonQuery();
                sqlCommand.Parameters.Clear();
            }
            catch (Exception ex)
            {
                MessageBox.Show("数据库查询失败\r\n错误:" + ex.Message + "\r\n跟踪:" + ex.StackTrace);
                sqlCommand.Parameters.Clear();
            }
        }

        static public void UpdateCustomerNickName(string nickName)
        {
            string strProcName = "EBSystem.SP_UpdateCustomerNickName";

            sqlCommand.CommandText = strProcName;

            sqlCommand.Parameters.Add("@customerAccountID", SqlDbType.BigInt);
            sqlCommand.Parameters["@customerAccountID"].Value = UserData.accountID;
            sqlCommand.Parameters.Add("@nickName", SqlDbType.NVarChar, 64);
            sqlCommand.Parameters["@nickName"].Value = nickName;

            sqlCommand.Parameters.Add("@return", SqlDbType.BigInt);
            sqlCommand.Parameters["@return"].Direction = ParameterDirection.ReturnValue;
            //sqlCommand.Parameters["@userName"].Direction = ParameterDirection.Output;

            //DataTable dataTable = new();
            //using (SqlDataAdapter adapter = new SqlDataAdapter(sqlCommand))
            //{
            //    adapter.Fill(dataTable);
            //}

            try
            {
                sqlCommand.ExecuteNonQuery();
                sqlCommand.Parameters.Clear();
            }
            catch (Exception ex)
            {
                MessageBox.Show("数据库查询失败\r\n错误:" + ex.Message + "\r\n跟踪:" + ex.StackTrace);
                sqlCommand.Parameters.Clear();
            }
        }

        static public void UpdateCustomerEmail(string email)
        {
            string strProcName = "EBSystem.SP_UpdateCustomerEmail";

            sqlCommand.CommandText = strProcName;

            sqlCommand.Parameters.Add("@customerAccountID", SqlDbType.BigInt);
            sqlCommand.Parameters["@customerAccountID"].Value = UserData.accountID;
            sqlCommand.Parameters.Add("@email", SqlDbType.NVarChar, 64);
            sqlCommand.Parameters["@email"].Value = email;

            sqlCommand.Parameters.Add("@return", SqlDbType.BigInt);
            sqlCommand.Parameters["@return"].Direction = ParameterDirection.ReturnValue;
            //sqlCommand.Parameters["@userName"].Direction = ParameterDirection.Output;

            //DataTable dataTable = new();
            //using (SqlDataAdapter adapter = new SqlDataAdapter(sqlCommand))
            //{
            //    adapter.Fill(dataTable);
            //}

            try
            {
                sqlCommand.ExecuteNonQuery();
                sqlCommand.Parameters.Clear();
            }
            catch (Exception ex)
            {
                MessageBox.Show("数据库查询失败\r\n错误:" + ex.Message + "\r\n跟踪:" + ex.StackTrace);
                sqlCommand.Parameters.Clear();
            }
        }

        static public void UpdateCustomerPhoneNumber(string phoneNumber)
        {
            string strProcName = "EBSystem.SP_UpdateCustomerPhoneNumber";

            sqlCommand.CommandText = strProcName;

            sqlCommand.Parameters.Add("@customerAccountID", SqlDbType.BigInt);
            sqlCommand.Parameters["@customerAccountID"].Value = UserData.accountID;
            sqlCommand.Parameters.Add("@phoneNumber", SqlDbType.Char, 11);
            sqlCommand.Parameters["@phoneNumber"].Value = phoneNumber;

            sqlCommand.Parameters.Add("@return", SqlDbType.BigInt);
            sqlCommand.Parameters["@return"].Direction = ParameterDirection.ReturnValue;
            //sqlCommand.Parameters["@userName"].Direction = ParameterDirection.Output;

            //DataTable dataTable = new();
            //using (SqlDataAdapter adapter = new SqlDataAdapter(sqlCommand))
            //{
            //    adapter.Fill(dataTable);
            //}

            try
            {
                sqlCommand.ExecuteNonQuery();
                sqlCommand.Parameters.Clear();
            }
            catch (Exception ex)
            {
                MessageBox.Show("数据库查询失败\r\n错误:" + ex.Message + "\r\n跟踪:" + ex.StackTrace);
                sqlCommand.Parameters.Clear();
            }
        }

        static public void AddDeliveryAddress(string province, string city, string county, string detailedAddress)
        {
            string strProcName = "EBSystem.SP_AddDeliveryAddress";

            sqlCommand.CommandText = strProcName;

            sqlCommand.Parameters.Add("@customerAccountID", SqlDbType.BigInt);
            sqlCommand.Parameters["@customerAccountID"].Value = UserData.accountID;

            sqlCommand.Parameters.Add("@province", SqlDbType.NVarChar, 8);
            sqlCommand.Parameters["@province"].Value = province;

            sqlCommand.Parameters.Add("@city", SqlDbType.NVarChar, 8);
            sqlCommand.Parameters["@city"].Value = city;

            sqlCommand.Parameters.Add("@county", SqlDbType.NVarChar, 15);
            sqlCommand.Parameters["@county"].Value = county;

            sqlCommand.Parameters.Add("@detailedAddress", SqlDbType.NVarChar, 100);
            sqlCommand.Parameters["@detailedAddress"].Value = detailedAddress;

            sqlCommand.Parameters.Add("@return", SqlDbType.BigInt);
            sqlCommand.Parameters["@return"].Direction = ParameterDirection.ReturnValue;
            //sqlCommand.Parameters["@userName"].Direction = ParameterDirection.Output;

            //DataTable dataTable = new();
            //using (SqlDataAdapter adapter = new SqlDataAdapter(sqlCommand))
            //{
            //    adapter.Fill(dataTable);
            //}

            try
            {
                sqlCommand.ExecuteNonQuery();
                sqlCommand.Parameters.Clear();
                LoadDeliveryAddress();
            }
            catch (Exception ex)
            {
                MessageBox.Show("数据库查询失败\r\n错误:" + ex.Message + "\r\n跟踪:" + ex.StackTrace);
                sqlCommand.Parameters.Clear();
            }
        }

        static public void DeleteDeliveryAddress(int index)
        {
            string strProcName = "EBSystem.SP_DeleteDeliveryAddress";

            sqlCommand.CommandText = strProcName;

            sqlCommand.Parameters.Add("@deliveryAddressID", SqlDbType.BigInt);
            sqlCommand.Parameters["@deliveryAddressID"].Value = UserData.deliveryAddressID[index];

            sqlCommand.Parameters.Add("@return", SqlDbType.BigInt);
            sqlCommand.Parameters["@return"].Direction = ParameterDirection.ReturnValue;
            //sqlCommand.Parameters["@userName"].Direction = ParameterDirection.Output;

            //DataTable dataTable = new();
            //using (SqlDataAdapter adapter = new SqlDataAdapter(sqlCommand))
            //{
            //    adapter.Fill(dataTable);
            //}

            try
            {
                sqlCommand.ExecuteNonQuery();
                sqlCommand.Parameters.Clear();
            }
            catch (Exception ex)
            {
                MessageBox.Show("数据库查询失败\r\n错误:" + ex.Message + "\r\n跟踪:" + ex.StackTrace);
                sqlCommand.Parameters.Clear();
            }
        }

        static public void SearchGoods(string goodsName, int pageNumber, int priceSort)
        {
            string strProcName = "EBSystem.SP_SearchGoods";

            sqlCommand.CommandText = strProcName;

            sqlCommand.Parameters.Add("@goodsName", SqlDbType.NVarChar, 64);
            sqlCommand.Parameters["@goodsName"].Value = goodsName;

            sqlCommand.Parameters.Add("@pageNumber", SqlDbType.Int);
            sqlCommand.Parameters["@pageNumber"].Value = pageNumber.ToString();

            sqlCommand.Parameters.Add("@priceSort", SqlDbType.Int);
            sqlCommand.Parameters["@priceSort"].Value = priceSort.ToString();

            sqlCommand.Parameters.Add("@return", SqlDbType.BigInt);
            sqlCommand.Parameters["@return"].Direction = ParameterDirection.ReturnValue;
            //sqlCommand.Parameters["@userName"].Direction = ParameterDirection.Output;

            UserData.goodsData = new();
            try
            {
                using (SqlDataAdapter adapter = new SqlDataAdapter(sqlCommand))
                {
                    adapter.Fill(UserData.goodsData);
                }
                sqlCommand.Parameters.Clear();
            }
            catch (Exception ex)
            {
                MessageBox.Show("数据库查询失败\r\n错误:" + ex.Message + "\r\n跟踪:" + ex.StackTrace);
                sqlCommand.Parameters.Clear();
            }
        }

        static public void CreateOrder(int index, string buyCount, int deliveryAddressIndex)
        {
            string strProcName = "EBSystem.SP_CreateOrder";

            sqlCommand.CommandText = strProcName;

            sqlCommand.Parameters.Add("@customerAccountID", SqlDbType.BigInt);
            sqlCommand.Parameters["@customerAccountID"].Value = UserData.accountID;

            sqlCommand.Parameters.Add("@businessAccountID", SqlDbType.BigInt);
            sqlCommand.Parameters["@businessAccountID"].Value = UserData.goodsData.Rows[index]["businessAccountID"].ToString();

            sqlCommand.Parameters.Add("@goodsID", SqlDbType.BigInt);
            sqlCommand.Parameters["@goodsID"].Value = UserData.goodsData.Rows[index]["goodsID"].ToString();

            sqlCommand.Parameters.Add("@goodsQuantity", SqlDbType.Int);
            sqlCommand.Parameters["@goodsQuantity"].Value = buyCount;

            sqlCommand.Parameters.Add("@deliveryAddressID", SqlDbType.BigInt);
            sqlCommand.Parameters["@deliveryAddressID"].Value = UserData.deliveryAddressID[deliveryAddressIndex];

            sqlCommand.Parameters.Add("@return", SqlDbType.BigInt);
            sqlCommand.Parameters["@return"].Direction = ParameterDirection.ReturnValue;

            try
            {
                sqlCommand.ExecuteNonQuery();
                sqlCommand.Parameters.Clear();
            }
            catch (Exception ex)
            {
                MessageBox.Show("数据库查询失败\r\n错误:" + ex.Message + "\r\n跟踪:" + ex.StackTrace);
                sqlCommand.Parameters.Clear();
            }
        }
    }
}
